import logging
import os
import pandas as pd
import datetime
import gradio as gr
from src.db.database import PostgreSQLConnector
from src.llm.sql_generator import SQLGenerator
from src.config.settings import settings
from src.ui.styles import get_custom_styles

class GradioInterface:
    """Gradio前端界面
    负责创建Web界面、处理用户输入和展示查询结果
    """
    def __init__(self):
        """初始化GradioInterface
        创建数据库连接器、SQL生成器并设置历史记录相关配置
        """
        self.db_connector = PostgreSQLConnector()
        self.sql_generator = SQLGenerator(self.db_connector)
        self.max_history = int(os.getenv("MAX_HISTORY", 10))
        self.history = []  # 存储查询历史

    def create_interface(self):
        """创建Gradio界面
        
        Returns:
            gr.Blocks: 构建好的Gradio界面实例
        """
        with gr.Blocks(title="智能问数", theme=gr.themes.Soft()) as demo:
            # 可替换的图标选项：📊、🔮、🤖、💡、🔍
            gr.Markdown("# 🤖智能问数演示")
            # 使用自定义样式
            gr.HTML(get_custom_styles())
            with gr.Row():
                with gr.Column(scale=1):
                    question_input = gr.Textbox(
                        label="🔍 请输入查询问题", 
                        placeholder="例如：查询销售额最高的前10个产品、各地区用户数量", 
                        lines=3,
                        elem_id="input-box"
                    )
                    with gr.Row():
                        generate_sql_btn = gr.Button("💡 生成SQL", variant="primary")
                        execute_sql_btn = gr.Button("▶️ 执行SQL", variant="primary")
                        export_btn = gr.Button("💾 导出文件", variant="primary")
                        clear_btn = gr.Button("🧹 清空", variant="secondary")
                    
                    # 移除自动下载开关控件
                    
                    gr.Markdown("## ⚙️ 系统配置列表")
                    gr.Textbox(
                        value=settings.get_ollama_config(),
                        label="LLM模型",
                        lines=2,
                        interactive=False
                    )
                    gr.Textbox(
                        value=settings.get_db_uri(),
                        label="数据库连接",
                        lines=2,
                        interactive=False
                    )
                    gr.Textbox(
                        value=settings.get_allowed_tables(),
                        label="数据库表",
                        lines=2,
                        interactive=False
                    )
                with gr.Column(scale=2):
                    with gr.Tab("📝 执行结果"):
                        # 可替换的图标选项：🔍、💻、📝
                        gr.Markdown("### 💻 AI生成的SQL")
                        sql_output = gr.Code(label="SQL语句", language="sql", lines=5, elem_id="sql-code")
                        
                        # 添加清洗后的SQL显示组件，用于导出功能
                        cleaned_sql = gr.Textbox(label="清洗后的SQL", visible=False)  # 设为不可见
                        
                        # 可替换的图标选项：📈、📊、✅
                        gr.Markdown("### ✅ SQL执行结果")
                        result_output = gr.Dataframe(
                            label="查询结果",
                            interactive=False,
                            column_widths="auto",
                            row_count=11,
                            headers=None,
                            wrap=True,
                            type="pandas",
                            elem_id="result-table",
                            elem_classes=["custom-table"]
                        )
                        
                        # 下载组件保持可见，用于显示可下载文件
                        download_file = gr.File(
                            label="可下载文件", 
                            visible=True,
                            elem_classes=["two-lines-file"]  # 应用自定义样式
                        )
                  
                    with gr.Tab("📋 查询历史"):
                        history_output = gr.Textbox(
                            label="历史记录",
                            lines=11,
                            interactive=False
                        )
            
            # 设置事件处理
            generate_sql_btn.click(
                fn=self.sql_generator.generate_sql,
                inputs=[question_input],
                outputs=[sql_output]
            )
            
            # 修改执行SQL按钮事件，移除自动下载逻辑
            execute_sql_btn.click(
                fn=self._execute_sql,   
                inputs=[sql_output],
                outputs=[cleaned_sql, result_output]
            )
            
            # 导出按钮事件处理，修改为直接触发下载
            export_btn.click(
                fn=self.handle_export,
                inputs=[cleaned_sql],
                outputs=[download_file]
            )
            
            clear_btn.click(
                fn=lambda: ("", "", pd.DataFrame(), None), 
                inputs=[],
                outputs=[question_input, sql_output, result_output, download_file]
            )
        
        return demo
    
    
    def _execute_sql(self, sql):
        """仅执行SQL并返回结果，不处理下载"""
        # 执行SQL获取结果
        cleaned_sql, result = self.sql_generator.execute_sql(sql)
        return cleaned_sql, result
    
    def export_data(self, query):
        """导出数据到文件并返回文件路径
        """
        if not query:
            return "没有数据可导出" 
        filename = 'query_'+datetime.datetime.now().strftime("%Y%m%d_%H%M%S")+".csv"
        filepath = self.db_connector.exp_data_scv(query, filename)
        logging.info(f"export_data exp_data_scv返回: {filepath}")
        
        # 检查返回值是否是成功的路径
        if filepath and isinstance(filepath, str) and not filepath.startswith("导出失败"):
            return  filepath
        else:
            # 如果返回的是错误消息，则使用该消息
            error_msg = filepath if filepath and isinstance(filepath, str) else f"{query} 导出失败"
            return error_msg
            
    def handle_export(self, cleaned_sql):
        """处理导出操作并确保下载框弹出
        Args:
            cleaned_sql (str): 清洗后的SQL查询语句
        Returns:
            str: 文件路径或None
        """
        if not cleaned_sql:
            gr.Warning("请先执行SQL获取结果")
            return None
            
        filepath = self.export_data(cleaned_sql)
        
        # 确保文件存在且返回正确路径以触发下载框
        if filepath and os.path.exists(filepath) and os.path.isfile(filepath):
            # 返回文件路径，Gradio会自动触发下载框
            return filepath
        else:
            gr.Error("文件导出失败，无法下载")
            return None
 
        
    def format_history(self, history):
        """格式化历史记录显示"""
        if not history:
            return "无查询历史"
            
        formatted = []
        for i, (q, sql, res) in enumerate(history, 1):
            formatted.append(f"[{i}] 问题: {q}\nSQL: {sql[:100]}...\n")
        return "\n".join(formatted)

    def clear_history(self):
        """清空历史记录"""
        self.history = []
        return "", "", []